Building an Interactive Dashboard with KPIs and Slicers

Course: Advanced Excel Training

Topic: Building an Interactive Dashboard with KPIs and Slicers

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Official Website: JustAcademy

1. Introduction to Interactive Excel Dashboards

An interactive dashboard in Microsoft Excel is a visual reporting tool that presents important business information in a compact, easy-to-understand format. It combines key performance indicators (KPIs), charts, summary tables, and interactive controls so users can explore data without manually editing formulas or filtering every worksheet.

KPIs highlight important measurements such as total sales, profit, revenue growth, order count, and target achievement. Slicers provide clickable buttons that filter connected PivotTables, PivotCharts, or supported Excel table data. When a user selects a region, product, month, or department, the dashboard can update to show the relevant information.

Interactive dashboards are widely used in sales reporting, finance, marketing analysis, inventory monitoring, human resources, and management reporting.

2. Learning Objectives

• Understand dashboard structure, KPIs, charts, and slicers.
• Prepare and organize a reliable source dataset.
• Convert raw data into an Excel Table.
• Create PivotTables and PivotCharts for dashboard analysis.
• Calculate important KPIs using formulas and PivotTable summaries.
• Insert slicers and connect them to multiple PivotTables.
• Design an interactive dashboard with a clear layout.
• Refresh data and troubleshoot common dashboard problems.
• Build a practical sales dashboard for business reporting.

3. What Is a Dashboard?

A dashboard is a single worksheet or report view that summarizes important information using visual elements and interactive controls. Instead of reviewing hundreds or thousands of rows, a user can quickly understand performance through KPIs, charts, and filters.

3.1 Main Dashboard Components

4. Understanding Key Performance Indicators (KPIs)

A Key Performance Indicator is a measurable value used to evaluate progress toward a business objective. A useful KPI has a clear definition, a reliable data source, and a meaningful interpretation.

4.1 Common Business KPIs

4.2 Characteristics of a Good KPI

• Relevant: It measures something connected to a business goal.
• Measurable: It can be calculated consistently from available data.
• Clear: Users understand what the KPI means.
• Comparable: It can be compared across periods, teams, or regions when appropriate.
• Actionable: It helps users identify where attention may be needed.
• Accurate: Its calculation and data source are verified.

5. Understanding Slicers in Excel

A slicer is a visual filtering control made up of selectable buttons. It lets users filter data by choosing values such as a region, product category, salesperson, or department. Slicers are particularly useful for interactive dashboards because users can see which values are selected.

5.1 Advantages of Slicers

• Make filtering easy for users who are not familiar with Excel's filter menus.
• Clearly show the current selection.
• Allow quick filtering by one or multiple categories.
• Can control multiple compatible PivotTables and PivotCharts.
• Improve dashboard usability and reduce manual filtering steps.

5.2 Slicers and Timelines

Important: Slicers created for PivotTables generally control other PivotTables only when they share a compatible PivotCache or data model and are connected through Report Connections. A slicer does not automatically control every chart or formula in a workbook.

6. Preparing the Source Dataset

A dashboard is only as reliable as the data behind it. Before creating KPIs or slicers, clean the dataset and ensure that every column has a consistent meaning.

6.1 Example Sales Dataset

Enter the following sample data into Excel, starting at cell A1.

6.2 Data Preparation Checklist

1. Ensure that every column has a unique, meaningful header.
2. Remove unnecessary blank rows and columns inside the dataset.
3. Check that dates are stored as Excel dates rather than inconsistent text.
4. Check that Sales and Cost contain numeric values.
5. Standardize category names such as North, South, East, and West.
6. Identify duplicate records and confirm whether they are genuine transactions.
7. Check for missing Order IDs, dates, sales values, and costs.
8. Confirm the meaning of each row and each measure before calculating KPIs.

6.3 Convert the Dataset into an Excel Table

1. Select any cell in the dataset.
2. Press Ctrl + T, or select Insert > Table.
3. Confirm the full data range.
4. Select My table has headers.
5. Click OK.
6. Open the Table Design tab and rename the table to SalesData.

Excel Tables expand when new rows are added and make formulas and source ranges easier to maintain. Always verify that new records are included in the dashboard's source data.

7. Calculating Important KPIs

KPIs can be calculated using formulas or summarized through PivotTables. For formula-based calculations, place the formulas in a dedicated calculation area or dashboard cells. The examples below assume the data is stored in an Excel Table named SalesData.

7.1 KPI Formula Examples

Note: The formulas above summarize the entire table. They do not automatically respond to PivotTable slicers. If KPI cards must update with slicer selections, calculate the KPIs from slicer-connected PivotTables or use an appropriate filtered-data calculation design.

7.2 Calculate Profit in a Helper Column

Add a column named Profit to the source table and enter:

=[@Sales]-[@Cost]

Excel automatically fills the formula down the table. The Profit column can then be used in PivotTables, charts, and KPI calculations.

7.3 Calculate Target Achievement

If a target amount is available in a separate cell, such as B2 on a target worksheet, and actual sales are in B3, use:

=IFERROR(B3/B2,0)

Format the result as a percentage. A result of 100% means the actual value equals the target; a result greater than 100% means the target has been exceeded.

8. Creating PivotTables for Dashboard Analysis

PivotTables summarize large datasets by grouping records and aggregating measures. They are useful for producing dashboard summaries without manually writing a separate formula for every category.

8.1 Create a Sales by Region PivotTable

1. Select a cell inside the SalesData table.
2. Choose Insert > PivotTable.
3. Select a new worksheet or a designated analysis worksheet.
4. Drag Region into Rows.
5. Drag Sales into Values.
6. Confirm that the value field is summarized by Sum rather than Count.
7. Rename the worksheet to Analysis if appropriate.

8.2 Create a Sales by Product PivotTable

1. Insert another PivotTable using the same source table.
2. Place Product in Rows.
3. Place Sales in Values.
4. Sort the sales values from largest to smallest if useful.
5. Apply number formatting to improve readability.

8.3 Create a Monthly Sales PivotTable

1. Insert a PivotTable from the same source data.
2. Place Date in Rows and Sales in Values.
3. Right-click a date value and choose Group, if the option is available.
4. Select Months and Years as appropriate.
5. Confirm that the dates are valid Excel dates and that grouping produces the intended reporting periods.

Using both Months and Years helps prevent January from different years being combined into one category.

9. Creating PivotCharts

PivotCharts are linked to PivotTables and help users explore summarized information visually. Their displayed results can respond to compatible slicers connected to their underlying PivotTables.

9.1 Recommended Chart Types

9.2 Insert a PivotChart

1. Select a cell inside the PivotTable.
2. Choose PivotTable Analyze > PivotChart, or use the relevant Insert Chart option in your Excel version.
3. Select an appropriate chart type.
4. Add a clear chart title, such as Sales by Region.
5. Remove unnecessary chart elements and check that labels are readable.
6. Move the chart to the dashboard worksheet or arrange it in the intended layout.

10. Adding Slicers to a Dashboard

10.1 Insert a Slicer

1. Select a cell inside the PivotTable you want to filter.
2. Choose PivotTable Analyze > Insert Slicer.
3. Select fields such as Region, Product, or Salesperson.
4. Click OK.
5. Position the slicer beside the relevant charts or at the top of the dashboard.
6. Use the slicer's buttons to test different selections.

10.2 Use a Slicer to Filter Data

For example, select North in the Region slicer. A connected PivotTable should show only records for the North region, and connected PivotCharts should update to reflect the filtered data.

To select multiple items, use the slicer's multi-select control if available, or hold Ctrl while selecting items, depending on your Excel version and configuration. Use the clear-filter icon to remove the slicer's current selection.

10.3 Connect One Slicer to Multiple PivotTables

1. Select the slicer.
2. Open the Slicer tab or the relevant slicer options in your Excel version.
3. Choose Report Connections or PivotTable Connections.
4. Check the PivotTables that the slicer should control.
5. Click OK and test the selection.

Important: If a PivotTable does not appear in the connection list, check whether it shares a compatible PivotCache or data model with the slicer's source. You may need to create the PivotTables from the same source or use a suitable common data model.

10.4 Add a Timeline for Date Filtering

1. Select a supported date-based PivotTable.
2. Choose PivotTable Analyze > Insert Timeline.
3. Select the Date field.
4. Click OK.
5. Choose the required time level, such as months, quarters, or years.
6. Use Timeline Connections or Report Connections to connect it to other compatible PivotTables.

Timelines are useful for date-based PivotTables but are not a universal filter for every formula or Excel Table in a workbook.

11. Designing the Dashboard Layout

A good dashboard should present information in a logical order. Users should be able to identify the reporting period, understand the main KPIs, compare categories, and apply filters without searching through multiple worksheets.

11.1 Suggested Dashboard Structure

11.2 Formatting Guidelines

• Use consistent fonts, number formats, and spacing.
• Give each KPI a short, descriptive title.
• Use the same number format for comparable values.
• Display currency values with a consistent currency symbol.
• Format percentages with an appropriate number of decimal places.
• Remove unnecessary gridlines from the dashboard worksheet if they distract from the content.
• Align KPI cards and charts to a clear grid.
• Use colors consistently to indicate categories or performance status.
• Avoid excessive colors, 3D effects, decorative shapes, and unnecessary chart labels.
• Ensure the dashboard remains readable when viewed at normal screen size.

12. Making KPI Cards Respond to Slicers

One important dashboard design decision is how to make KPI values respond to slicer selections. Standard formulas such as =SUM(SalesData[Sales]) calculate from the source table and do not automatically inherit a PivotTable slicer's filter state.

Method 1: Use PivotTable Values

1. Create a PivotTable that summarizes the required KPI.
2. Connect the relevant slicers to that PivotTable.
3. Place the KPI summary in a dedicated dashboard area.
4. Format the result as a KPI card using a clear label and appropriate number format.

This is a practical approach when the KPI can be represented by a PivotTable aggregation.

Method 2: Use GETPIVOTDATA

GETPIVOTDATA can retrieve a value from a PivotTable. For example, a formula referencing a PivotTable whose top-left cell is B3 may look like this:

=GETPIVOTDATA("Sales",$B$3)

The exact field caption and PivotTable reference must match your workbook. If the formula retrieves a grand total from a slicer-connected PivotTable, the result can reflect the PivotTable's current filter state. Test the formula after changing the slicer.

Method 3: Use a Formula-Based Filtered Calculation

In some designs, formula-based KPIs can use explicit selection cells and criteria-based functions such as SUMIFS or COUNTIFS. This approach requires the formulas to refer to the selected criteria directly. It is not automatically connected to a PivotTable slicer unless an appropriate integration is built.

Best practice: Decide which KPI values should change with the dashboard filters, then test every KPI after changing each slicer and timeline. A dashboard is not fully interactive if charts update but the KPI cards remain unchanged when users expect them to respond.

13. Creating a Complete Interactive Sales Dashboard

Step 1: Create the Source Sheet

Rename the source worksheet to RawData. Store the cleaned sales records in the SalesData Excel Table.

Step 2: Create an Analysis Sheet

Rename another worksheet to Analysis. Build PivotTables for sales by region, sales by product, monthly sales, and sales by salesperson.

Step 3: Create Charts

Insert PivotCharts from the relevant PivotTables. Use a line chart for monthly sales and column or bar charts for category comparisons.

Step 4: Add Slicers

Insert slicers for Region, Product, and Salesperson. Connect each slicer to the PivotTables that should respond to it.

Step 5: Add a Timeline

Insert a timeline based on the Date field if supported by the PivotTable source. Connect it to compatible PivotTables.

Step 6: Create KPI Summaries

Use PivotTable totals, GETPIVOTDATA, or deliberately designed formula-based calculations for Total Sales, Total Profit, Profit Margin, and Order Count. Confirm that the KPI values respond to the required filters.

Step 7: Build the Dashboard Worksheet

Create a worksheet named Dashboard. Position the KPI cards at the top, slicers near the top or left side, and charts below the KPIs.

Step 8: Format the Dashboard

Apply consistent number formats, chart titles, fonts, and alignment. Remove unnecessary worksheet elements and ensure that slicer buttons are easy to click.

Step 9: Test the Interactions

Select different regions, products, salespeople, and date ranges. Confirm that every connected chart, summary, and KPI changes as expected.

Step 10: Refresh and Validate

Add a test record to the source table, refresh the relevant PivotTables, and check that the new record is included. Verify totals against the raw data before sharing the workbook.

14. Refreshing Dashboard Data

PivotTables generally need to be refreshed when source data changes. Excel Tables can expand as new rows are added, but the PivotTable summaries still need updating.

Refresh Methods

• Right-click a PivotTable and choose Refresh.
• Use Data > Refresh All to refresh supported connections and PivotTables.
• Check that the source range includes the new rows.
• For a workbook that uses external data connections, verify that the connection refresh completes successfully.
• Consider refresh-on-open settings when the workbook's usage and data source make this appropriate.

Validation tip: After refreshing, compare key totals on the dashboard with a direct calculation from the source dataset. This helps identify missing rows, duplicate records, or incorrect aggregations.

15. Troubleshooting Common Dashboard Problems

16. Dashboard Security and Sharing

• Save the workbook in an appropriate Excel format, such as .xlsx, when macros are not required.
• Keep raw data and calculation worksheets organized and clearly named.
• Protect worksheets when necessary to prevent accidental changes to formulas or layout.
• Remember that worksheet protection is not the same as strong file encryption or access control.
• Remove confidential information that dashboard viewers do not need.
• Test slicers, charts, formulas, and print settings before sharing the workbook.
• Document the data source, reporting period, refresh date, and KPI definitions.

17. Practical Exercises

Exercise 1: Create KPI Cards

Using the sample sales dataset, create cards for Total Sales, Total Cost, Total Profit, Profit Margin, and Order Count.

• Use appropriate formulas or PivotTable summaries.
• Apply consistent number formatting.
• Write a short definition for each KPI.

Exercise 2: Build Sales Comparison Charts

Create charts showing sales by region, sales by product, and monthly sales trends.

• Choose an appropriate chart type for each comparison.
• Add descriptive titles.
• Confirm that totals match the underlying PivotTables.

Exercise 3: Add Interactive Slicers

Insert slicers for Region, Product, and Salesperson.

• Connect slicers to multiple compatible PivotTables.
• Test a single selection and a multi-selection.
• Clear the filters and confirm that all records are included again.

Exercise 4: Add a Date Timeline

Create a timeline from the Date field and connect it to the relevant PivotTables.

• View one month at a time.
• Compare different quarters or years when the dataset includes them.
• Check whether all expected charts respond to the timeline.

18. Mini Project: Interactive Sales Performance Dashboard

Project Objective: Build a professional Excel dashboard that allows a manager to explore sales performance using KPIs, charts, slicers, and date filters.

Project Requirements

1. Prepare a clean sales dataset and convert it into an Excel Table.
2. Create PivotTables for sales by region, product, salesperson, and month.
3. Build KPI summaries for total sales, profit, profit margin, and order count.
4. Insert PivotCharts to display comparisons and trends.
5. Add slicers for region, product, and salesperson.
6. Add a timeline for date-based analysis where supported.
7. Connect slicers to all relevant PivotTables and charts.
8. Make KPI cards respond to filters where expected.
9. Use consistent colors, readable titles, and suitable number formats.
10. Test the workbook with different selections and after adding new records.
11. Document the KPI definitions and data refresh process.

Expected Deliverables

• A cleaned and structured source-data worksheet.
• An analysis worksheet containing the PivotTables.
• A dashboard worksheet containing KPI cards, charts, and slicers.
• A working interactive filter system.
• A short validation checklist showing that the dashboard totals and interactions have been tested.

19. Interview Questions and Answers

Q1. What is an Excel dashboard?

Answer: An Excel dashboard is a visual report that summarizes important information through KPIs, charts, tables, and interactive filters.

Q2. What is a KPI?

Answer: A Key Performance Indicator is a measurable value used to assess progress toward a business objective, such as sales target achievement or profit margin.

Q3. What is a slicer in Excel?

Answer: A slicer is a visual filtering control that lets users filter supported Excel Tables or PivotTables by selecting buttons for specific field values.

Q4. How do you connect one slicer to multiple PivotTables?

Answer: Select the slicer, open Report Connections or PivotTable Connections, and select the compatible PivotTables that should respond to it.

Q5. Why might a slicer fail to control a PivotTable?

Answer: The PivotTable may not be connected to the slicer or may use an incompatible PivotCache or data model.

Q6. What is the difference between a slicer and a timeline?

Answer: Slicers generally filter categories such as region and product. Timelines provide date-based filtering for supported PivotTables.

Q7. Why does a formula-based KPI sometimes remain unchanged when a slicer is used?

Answer: A normal formula such as SUM over the entire source table does not automatically inherit a PivotTable slicer's filter. The KPI must use a slicer-aware calculation design.

Q8. What is GETPIVOTDATA used for?

Answer: GETPIVOTDATA retrieves specific summarized values from a PivotTable and can be useful for building KPI cards linked to filtered PivotTable results.

Q9. Why should source data be converted into an Excel Table?

Answer: Tables provide structured references, consistent headers, and automatic range expansion when new rows are added. PivotTables may still require refreshing.

Q10. How do you maintain dashboard accuracy?

Answer: Clean the source data, define KPIs clearly, verify formulas, refresh PivotTables, test slicer connections, and compare dashboard totals with the source data.

20. Quick Revision Checklist

• Understand dashboard components and the purpose of KPIs.
• Prepare clean data with consistent headers and data types.
• Convert the source range into an Excel Table.
• Create PivotTables for summaries and PivotCharts for visual analysis.
• Insert slicers for categorical filtering.
• Connect slicers through Report Connections when supported.
• Use a timeline for supported date-based PivotTables.
• Make sure KPI cards respond to filters when expected.
• Refresh PivotTables after source data changes.
• Test formulas, totals, filters, and layout before sharing the workbook.

21. Conclusion

Building an interactive dashboard with KPIs and slicers is an important Advanced Excel skill. KPIs communicate business performance, PivotTables summarize the data, charts reveal patterns, and slicers help users explore information by category or reporting period.

A successful dashboard combines accurate calculations, clean source data, a logical layout, reliable filter connections, and regular validation. By completing the practical exercises and mini project in these notes, learners can develop a reusable dashboard for sales analysis and other business reporting tasks.

22. Additional Learning Resources

• Microsoft Excel Help and Learning
• Microsoft Support: Use Slicers to Filter Data
• Microsoft Support: Create a PivotTable

23. Learn Advanced Excel with JustAcademy

Explore the Advanced Excel course to strengthen your skills in formulas, data analysis, PivotTables, charts, dashboards, and interactive reporting.

Course Page: View the Advanced Excel Course

Course Demo: Register for a Course Demo

Official Website: Visit JustAcademy

End of Notes: Building an Interactive Dashboard with KPIs and Slicers